import pymssql


class MssqlHelper:
    def __init__(self, host, port, user, password, database):
        # 连接数据库
        self.conn = pymssql.connect(host=host,
                                    user=user,
                                    port= port,
                                    password=password,
                                    charset='UTF-8',
                                    database=database,tds_version="7.0")
        self.database = database

    def get_databases(self):
        sql = "SELECT name FROM sys.databases;"
        cur = self.conn.cursor(as_dict=True)
        # 执行SQL查询
        cur.execute(sql)
        rows = cur.fetchall()
        cur.close()
        return rows

    def get_tables(self):
        # 创建连接
        cur = self.conn.cursor(as_dict=True)
        # 执行SQL查询
        cur.execute("SELECT table_name as name FROM INFORMATION_SCHEMA.TABLES WHERE table_type = 'BASE TABLE' Order By table_name;")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = []
        # print(rows)
        for row in rows:
            data_list.append(row)
        return data_list

    def get_colum_list(self, name):
        # 创建连接
        cur = self.conn.cursor(as_dict=True)
        # 执行SQL查询
        cur.execute(f"""
         SELECT   B.NAME As name,C.NAME As col_type,B.PREC As col_len,B.SCALE as digits,
            CASE WHEN NOT F.ID IS NULL THEN '1' ELSE '' END pk,B.isnullable,
            CASE WHEN COLUMNPROPERTY(B.ID,B.NAME,'ISIDENTITY') = 1 THEN '1' ELSE '' END AS autoup,
            CONVERT(VARCHAR(1000),ISNULL(G.VALUE,'')) col_desc
        FROM SYSOBJECTS A INNER JOIN SYSCOLUMNS B ON A.ID=B.ID INNER JOIN SYSTYPES C ON B.XTYPE=C.XUSERTYPE
            LEFT JOIN SYSOBJECTS D ON B.ID=D.PARENT_OBJ AND D.XTYPE='PK'
            LEFT JOIN SYSINDEXES E ON B.ID=E.ID AND D.NAME=E.NAME
            LEFT JOIN SYSINDEXKEYS F ON B.ID=F.ID AND B.COLID=F.COLID AND E.INDID=F.INDID
            LEFT JOIN SYS.EXTENDED_PROPERTIES G ON B.ID=G.MAJOR_ID AND B.COLID=G.MINOR_ID
        WHERE A.XTYPE='U' AND OBJECT_NAME(B.ID)='{name}'""")
        # 获取查询结果
        rows = cur.fetchall()
        cur.close()
        data_list = []
        for row in rows:
            len = str(row['col_len'])
            if row['digits']:
                len = f"{len},{row['digits']}"

            data = {
                "col_type": row["col_type"],
                "defval": "",
                "desc": row["col_desc"],
                "len": len,
                "name": row["name"],
                "nullable": row["isnullable"],
                "pk": row["pk"]
            }
            data_list.append(data)
        # print(data_list)
        return data_list

    def update_column_desc(self,table_name:str,col_name, desc:str):
        sql =f"""
            IF ((SELECT COUNT(*) FROM ::fn_listextendedproperty('MS_Description',
            'SCHEMA', 'dbo',
            'TABLE', N'{table_name}',
            'COLUMN', N'{col_name}')) > 0)
              EXEC sp_updateextendedproperty
            'MS_Description', N'{desc}',
            'SCHEMA', 'dbo',
            'TABLE', N'{table_name}',
            'COLUMN', N'{col_name}'
            ELSE
              EXEC sp_addextendedproperty
            'MS_Description', N'{desc}',
            'SCHEMA', 'dbo',
            'TABLE', N'{table_name}',
            'COLUMN', N'{col_name}'
            """
        cur = self.conn.cursor()
        print(sql)
        # 执行SQL查询
        cur.execute(sql)
        cur.close()
        self.conn.commit()
        return {"code":1,"msg":"update ok"}


    def __del__(self):
        self.conn.close()